package com.cqeec.action;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;

import com.cqeec.bean.BookInfo;
import com.cqeec.util.DBUtil;

/**
 * 
 * @author HuaL
 * @description 图书控制器
 */
public class BookAction {
	Connection con = null;
	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	public BookAction() {
		con = DBUtil.getConnection();
	}

	/**
	 * 
	 * @param 图书对象
	 * @return 存放图书信息的二维数组
	 */
	public String[][] getBookList(BookInfo info) {
		String sql = "";
		sql = "SELECT * FROM BookInfo where 1=1 ";
		if (!info.getBookid().trim().equals("")) {
			sql = sql + " and bookid='" + info.getBookid().trim() + "'";
		}
		if (!info.getBookname().trim().equals("")) {
			sql = sql + " and bookname like '%" + info.getBookname().trim()
					+ "%'";
		}
		if (!info.getBooktype().trim().equals("")) {
			sql = sql + " and booktype='" + info.getBooktype().trim() + "'";
		}
		if (!info.getAuthor().trim().equals("")) {
			sql = sql + " and author like '%" + info.getAuthor().trim() + "%'";
		}
		if (!info.getPublisher().trim().equals("")) {
			sql = sql + " and publisher like '%" + info.getPublisher().trim()
					+ "%'";
		}
		if (!info.getStartDate().trim().equals("")) {
			sql = sql + " and publishdate>='" + info.getStartDate().trim()
					+ "'";
		}
		if (!info.getEndDate().trim().equals("")) {
			sql = sql + " and publishdate<='" + info.getEndDate().trim() + "'";
		}
		if (!info.getMinPrice().trim().equals("")) {
			sql = sql + " and price>=" + info.getMinPrice().trim();
		}
		if (!info.getMaxPrice().trim().equals("")) {
			sql = sql + " and price<=" + info.getMaxPrice().trim();
		}
		ResultSet rs = null;
		String sn[][] = null;
		int row = 0;
		try {
			Statement stat = con.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stat.executeQuery(sql);
			if (rs.last()) {
				row = rs.getRow();
			}
			if (row != 0) {
				sn = new String[row][8];
				rs.first();
				rs.previous();
				int i = 0;
				while (rs.next()) {
					sn[i][0] = rs.getString("bookid");
					sn[i][1] = rs.getString("bookname");
					sn[i][2] = rs.getString("booktype");
					sn[i][3] = rs.getString("stocknum");
					sn[i][4] = rs.getString("author");
					sn[i][5] = sdf.format(rs.getDate("publishdate"));
					sn[i][6] = rs.getString("publisher");
					sn[i][7] = rs.getString("price");
					i++;
				}
				rs.close();
				stat.close();
			}
		} catch (Exception e) {
		}
		return sn;
	}
	/**
	 * 
	 * @param info 图书对象
	 * @return  添加成功返回true，否则返回false
	 */
	public boolean add(BookInfo info) {
		String sql = "Insert into BookInfo(bookid,bookname,stocknum,price,publishdate,author,booktype,publisher) values(?,?,?,?,?,?,?,?)";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, info.getBookid().trim());
			pstm.setString(2, info.getBookname().trim());
			pstm.setString(3, info.getStocknum());
			pstm.setString(4, info.getPrice().trim());
			pstm.setString(5, info.getPublishdate().trim());
			pstm.setString(6, info.getAuthor().trim());
			pstm.setString(7, info.getBooktype().trim());
			pstm.setString(8, info.getPublisher().trim());
			pstm.executeUpdate();
			pstm.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return false;
		}
	}
	/**
	 * 
	 * @param info 图书对象
	 * @return  修改成功返回true，否则返回false
	 */
	public boolean update(BookInfo info) {
		String sql = "UPDATE BookInfo set bookname=?,stocknum=?,price=?,publishdate=?,author=?,booktype=?,publisher=? where bookid=?";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, info.getBookname().trim());
			pstm.setString(2, info.getStocknum());
			pstm.setString(3, info.getPrice().trim());
			pstm.setString(4, info.getPublishdate().trim());
			pstm.setString(5, info.getAuthor().trim());
			pstm.setString(6, info.getBooktype().trim());
			pstm.setString(7, info.getPublisher().trim());
			pstm.setString(8, info.getBookid().trim());
			pstm.executeUpdate();
			pstm.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return false;
		}
	}
	/**
	 * 
	 * @param bookid 图书编号
	 * @return  删除成功返回true，否则返回false
	 */
	public boolean del(String bookid) {
		String sql = "Delete BookInfo where bookid=?";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, bookid);
			pstm.executeUpdate();
			pstm.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return false;
		}
	}
	/**
	 * 
	 * @param bookid 图书编号
	 * @return 添加图书时若存在返回false，否则返回true
	 */
	public boolean beforeadd(String bookid){
		String sql = "Select count(*) from BookInfo where bookid=?";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, bookid);
			ResultSet rs = pstm.executeQuery();
			if (rs.next()){
				int count = rs.getInt(1);
				if (count == 0){
					return true;
				}else{
					return false;
				}
			}else{
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return false;
		}
	}
	/**
	 * 
	 * @param bookid 图书编号
	 * @return 删除图书时，若在借阅信息表中存在返回false，否则返回true
	 */
	public boolean beforedel(String bookid){
		String sql = "select count(*) from LendingInfo where bookid=?";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, bookid);
			ResultSet rs = pstm.executeQuery();
			if (rs.next()){
				int count = rs.getInt(1);
				if (count == 0){
					return true;
				}else{
					return false;
				}
			}else{
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return false;
		}
	}
	/**
	 * 
	 * @param readerid 读者编号
	 * @return 读者信息对象
	 */
	public BookInfo getBookInfoByID(String bookid){
		BookInfo bookInfo = null;
		String sql = "select * from BookInfo where bookid=?";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, bookid);
			ResultSet rs = pstm.executeQuery();
			if (rs.next()){
				bookInfo = new BookInfo();
				bookInfo.setBookname(rs.getString("bookname"));
				bookInfo.setPrice(rs.getString("price"));
				bookInfo.setBooktype(rs.getString("booktype"));
				bookInfo.setAuthor(rs.getString("author"));
				bookInfo.setPublishdate(sdf.format(rs.getDate("publishdate")));
				bookInfo.setPublisher(rs.getString("publisher"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
		}
		return bookInfo;
	}
}
